package edu.uoc.tdp.pac4.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import edu.uoc.tdp.pac4.beans.Registration;


public class RegistrationManager {

	Connection con = null;
	PreparedStatement ptmt = null;
	ResultSet rs = null;

	public RegistrationManager() {
		super();
	}

	private Connection getConnection() throws SQLException {
		Connection conn=null;
			conn = DiskManager.getInstance().getConnection();
		return conn;
	}

	

	public List<Registration> getAllRegByStudent(String nif){
		
		List<Registration> registrations = new ArrayList<Registration>();
		Registration registration = null;
		try {
			String q = "SELECT matriculaID, usuarioId, cursoId, grupoId, estadoId FROM matriculas WHERE usuarioId = ?";
			con = getConnection();
			ptmt = con.prepareStatement(q);
			ptmt.setString(1, nif);
			rs = ptmt.executeQuery();
			while (rs.next()) {

				registration = new Registration();  
				registration.setRegistrationId(rs.getInt(1));
				registration.setUserId(rs.getString(2));
				registration.setCourseId(rs.getInt(3));
				registration.setGroupId(rs.getInt(4));
				registration.setStatus(rs.getInt(5));
				registrations.add(registration);
			
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
				rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return registrations;
	}
	
	public List<Registration> getAcceptedRegByStudent(String nif){
		
		List<Registration> registrations = new ArrayList<Registration>();
		Registration registration = null;
		try {
			String q = "SELECT matriculaID, usuarioId, cursoId, grupoId, estadoId FROM matriculas WHERE usuarioId = ? and estadoId = 1";
			con = getConnection();
			ptmt = con.prepareStatement(q);
			ptmt.setString(1, nif);
			rs = ptmt.executeQuery();
			while (rs.next()) {

				registration = new Registration();  
				registration.setRegistrationId(rs.getInt(1));
				registration.setUserId(rs.getString(2));
				registration.setCourseId(rs.getInt(3));
				registration.setGroupId(rs.getInt(4));
				registration.setStatus(rs.getInt(5));
				registrations.add(registration);
			
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
				rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return registrations;
	}

	public List<Registration> getPendingRegByStudent(String nif){
	
	List<Registration> registrations = new ArrayList<Registration>();
	Registration registration = null;
	try {
		String q = "SELECT matriculaID, usuarioId, cursoId, grupoId, estadoId FROM matriculas WHERE usuarioId = ? and estadoId = 0";
		con = getConnection();
		ptmt = con.prepareStatement(q);
		ptmt.setString(1, nif);
		rs = ptmt.executeQuery();
		while (rs.next()) {

			registration = new Registration();  
			registration.setRegistrationId(rs.getInt(1));
			registration.setUserId(rs.getString(2));
			registration.setCourseId(rs.getInt(3));
			registration.setGroupId(rs.getInt(4));
			registration.setStatus(rs.getInt(5));
			registrations.add(registration);
		
	}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			if (rs != null)
			rs.close();
			if (ptmt != null)
				ptmt.close();
			if (con != null)
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	return registrations;
}

	public List<Registration> getDeclinedRegByStudent(String nif){
	
	List<Registration> registrations = new ArrayList<Registration>();
	Registration registration = null;
	try {
		String q = "SELECT matriculaID, usuarioId, cursoId, grupoId, estadoId FROM matriculas WHERE usuarioId = ? and estadoId = 2";
		con = getConnection();
		ptmt = con.prepareStatement(q);
		ptmt.setString(1, nif);
		rs = ptmt.executeQuery();
		while (rs.next()) {

			registration = new Registration();  
			registration.setRegistrationId(rs.getInt(1));
			registration.setUserId(rs.getString(2));
			registration.setCourseId(rs.getInt(3));
			registration.setGroupId(rs.getInt(4));
			registration.setStatus(rs.getInt(5));
			registrations.add(registration);
		
	}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			if (rs != null)
			rs.close();
			if (ptmt != null)
				ptmt.close();
			if (con != null)
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	return registrations;
}

	/**
	 * Fins all registrations
	 * @return
	 * @throws Exception
	 */
	public List<Registration> findAllRegistrations(){
		List<Registration> registrations = new ArrayList<Registration>();
		Registration registration = null;
		try {
			String querystring = "SELECT matriculaId, usuarioId, cursoId, grupoId, estadoId FROM matriculas";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			rs = ptmt.executeQuery();
			while (rs.next()) {

				registration = new Registration();  
				registration.setRegistrationId(rs.getInt(1));
				registration.setUserId(rs.getString(2));
				registration.setCourseId(rs.getInt(3));
				registration.setGroupId(rs.getInt(4));
				registration.setStatus(rs.getInt(5));
				registrations.add(registration);
			
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
				rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return registrations;
	}

	/**
	 * Finds a registration by its primary key
	 * @param matriculaid
	 * @return
	 * @throws Exception
	 */
	public Registration findRegByPrimaryKey(int matriculaid) throws Exception {
		Registration registration = null;

		try {
			String querystring = "SELECT * FROM matriculas WHERE matriculaid=?";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setInt(1, matriculaid);
			rs = ptmt.executeQuery();
			if (rs.next()) {
				registration = new Registration();
				registration.setRegistrationId(rs.getInt(1));
				registration.setRequestId(rs.getInt(2));
				registration.setCourseId(rs.getInt(3));
				registration.setGroupId(rs.getInt(4));
				registration.setStatus(rs.getInt(5));
				registration.setUserId(rs.getString(6));
				
				
				

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return registration;
	}
	
	/**
	 * Check if the registration already exists
	 * @param registration
	 */
	
	public boolean checkNewRegistration(Registration registration){

		String q = "SELECT * FROM matriculas WHERE cursoid = ? and usuarioid = ?";
		try {
			con = getConnection();
			ptmt = con.prepareStatement(q);
			ptmt.setInt(1, registration.getCourseId());
			ptmt.setString(2, registration.getUserId());
			rs = ptmt.executeQuery();
			
			if(rs.next())
				return false;
			else{
				this.insertNewRegistration(registration);
				return true;
				}
			} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	
	/**
	 * If registration doesn't exist, it can be inserted
	 * @param registration
	 */
	public void insertNewRegistration(Registration registration){
		

		try{
			
			String q = "INSERT INTO Matriculas(peticionid, cursoid, grupoid, estadoid, usuarioid, fechamodificacion, fechaalta)" + "VALUES(?,?,?,?,?,?,?)";
			ptmt = con.prepareStatement(q);
			ptmt.setInt(1, registration.getRequestId());
			ptmt.setInt(2, registration.getCourseId());
			ptmt.setInt(3, registration.getGroupId());
			ptmt.setInt(4, registration.getStatus());		
			ptmt.setString(5,registration.getUserId());
			Date date = new Date();
			ptmt.setDate(6,new java.sql.Date(date.getTime()));
			ptmt.setDate(7,new java.sql.Date(date.getTime()));
			ptmt.execute();
		} catch (SQLException e) {
		e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}	
	}
	
	/**
	 * Updates a registration
	 * @param registration
	 */
	public void updateRegistration(Registration registration) {
		try {
			String querystring = "UPDATE matriculas SET estadoid=?, grupoid=? "
					+ "WHERE matriculaid=?";
			
	

				
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setInt(1, registration.getStatus());
			ptmt.setInt(2,  registration.getGroupId());
			ptmt.setInt(3, registration.getRegistrationId());
			
				
			
			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		
	}

	

	/**
	 * Filters registrations according to nif, course and status
	 * @param nif
	 * @param cursId
	 * @param statusint
	 * @return
	 */
	public List<Registration> find(String nif, int cursId,  int statusint) {
		List<Registration> registrations = new ArrayList<Registration>();
		Registration registration = null;
		String s = "";
		
		try {

			String querystring = "SELECT matriculaId, usuarioId, cursoId, grupoId, estadoId FROM matriculas "
					+ "WHERE '0'='0' ";
			querystring += " AND ( '0'='" + ((nif.equals(s)) ? 0 : 1)
					+ "' OR  usuarioId = ? ) ";
			querystring += " AND ( '0'='" + ((cursId == 0) ? 0 : 1)
					+ "' OR  cursoId = ? ) ";
			querystring += " AND ( '0'='" + ((statusint == -1) ? 0 : 1)
					+ "' OR  estadoId = ? ) ";
			

			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, nif);
			ptmt.setInt(2, cursId);
			ptmt.setInt(3, statusint);


			rs = ptmt.executeQuery();
			while (rs.next()) {
				
				registration = new Registration();
				registration.setRegistrationId(rs.getInt(1));
				registration.setUserId(rs.getString(2));
				registration.setCourseId(rs.getInt(3));
				registration.setGroupId(rs.getInt(4));
				registration.setStatus(rs.getInt(5));
				registrations.add(registration);
				

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return registrations;
	}

	public int getAcceptedRegByGroup(int groupid) {
		List<Registration> registrations = new ArrayList<Registration>();
		Registration registration = null;
		int total = 0;
		try {
			String q = "SELECT matriculaID, usuarioId, cursoId, grupoId, estadoId FROM matriculas WHERE grupoId = ? and estadoId = 1 ";
			con = getConnection();
			ptmt = con.prepareStatement(q);
			ptmt.setInt(1, groupid);
			rs = ptmt.executeQuery();
			while (rs.next()) {
				total = total + 1;
			
				registration = new Registration();  
				registration.setRegistrationId(rs.getInt(1));
				registration.setUserId(rs.getString(2));
				registration.setCourseId(rs.getInt(3));
				registration.setGroupId(rs.getInt(4));
				registration.setStatus(rs.getInt(5));
				registrations.add(registration);
			
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
				rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return total;
	}


}
